﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using Osiptel.HECO.BE;
using Osiptel.HECO.BL;
using System.IO;
using Microsoft.Office.Interop.Excel;
using System.Configuration;
using System.Drawing;

/// <summary>
/// Descripción breve de ExcelApp
/// </summary>
public class ExcelApp
{
    private int _inicioCelda = 1;
    private int _inicioFila = 2;

    public ExcelApp()
    {

    }

    public string GenerarExcel(List<ComponenteBE> datos, string tipo)
    {
        Application ExcelApp = new Application();
        Workbook ExcelWorkBook = null;
        Worksheet ExcelWorkSheet = null;
        var url = ConfigurationManager.AppSettings["urlExcel"].ToString();
        ExcelApp.Workbooks.Add();
        ExcelWorkSheet = (Worksheet)ExcelApp.ActiveSheet;
        string nombre = "";
        //ExcelApp.Visible = true;
        //ExcelWorkBook = ExcelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
        // ExcelWorkBook.Worksheets.Add(); //Adding New Sheet in Excel Workbook
        try
        {
            //ExcelWorkSheet.Cells[_inicioCelda, "A"] = "Name";
            //ExcelWorkSheet.Cells[_inicioCelda, "A"] = "Color";
            //ExcelWorkSheet.Cells[_inicioCelda, "A"] = "Maximum speed";
            ExcelWorkSheet.Cells[_inicioCelda, "A"] = "ID";
            ExcelApp.Range["A" + _inicioCelda].EntireColumn.ColumnWidth = 3;
            ExcelWorkSheet.Cells[_inicioCelda, "B"] = "Descripción";
            ExcelApp.Range["B" + _inicioCelda].EntireColumn.ColumnWidth = 40;
            ExcelApp.Range["B" + _inicioCelda].EntireColumn.WrapText = true;
            //ExcelWorkSheet.Cells[_inicioCelda, "C"] = "Unidad";
            ExcelApp.Range["C" + _inicioCelda].EntireColumn.ColumnWidth = 0;
            ExcelWorkSheet.Cells[_inicioCelda, "D"] = "Cantidad";
            ExcelApp.Range["D" + _inicioCelda].EntireColumn.ColumnWidth = 10;
            ExcelWorkSheet.Cells[_inicioCelda, "E"] = "Precio Unitario";
            ExcelApp.Range["E" + _inicioCelda].EntireColumn.ColumnWidth = 15;
            ExcelWorkSheet.Cells[_inicioCelda, "F"] = "Precio Total";
            ExcelApp.Range["F" + _inicioCelda].EntireColumn.ColumnWidth = 12;
            //ExcelWorkSheet.Cells[_inicioCelda , "G"] = "Fuente";
            ExcelApp.Range["G" + _inicioCelda].EntireColumn.ColumnWidth = 0;
            ExcelWorkSheet.Cells[_inicioCelda, "h"] = "Precio Total Mínimo";
            ExcelApp.Range["H" + _inicioCelda].EntireColumn.ColumnWidth = 20;
            ExcelWorkSheet.Cells[_inicioCelda, "I"] = "Precio Total Máximo";
            ExcelApp.Range["I" + _inicioCelda].EntireColumn.ColumnWidth = 20;
            int row = _inicioFila; // start row (in row 1 are header cells)

            var datosGrupo = datos.GroupBy(x => x.P07ClaseDesc).Select(t => new { count = t.Count(), key = t.Key });
            List<ComponenteBE> datos1 = new List<ComponenteBE>();
            int indiceAnterior = 0;
            for (int r = 0; r < datos.Count; r++)
            {
                if (r == 0)
                {
                    ComponenteBE temp = new ComponenteBE();
                    temp.P07ClaseDesc = datos[r].P07ClaseDesc;
                    temp.Estado = "head";
                    datos1.Add(temp);
                }
                if (datos[indiceAnterior].P07ClaseDesc != datos[r].P07ClaseDesc)
                {
                    ComponenteBE temp = new ComponenteBE();
                    temp.P07ClaseDesc = datos[r].P07ClaseDesc;
                    temp.Estado = "head";
                    ComponenteBE subtotal = new ComponenteBE();
                    subtotal.Estado = "subtotal";
                    datos1.Add(subtotal);
                    datos1.Add(temp);
                }
                datos1.Add(datos[r]);
                indiceAnterior = r;
            }
            ComponenteBE subtotal1 = new ComponenteBE();
            subtotal1.Estado = "subtotal";
            datos1.Add(subtotal1);
            foreach (var group in datosGrupo)
            {

                Console.WriteLine(group.key + " " + group.count);
            }
            int tam = datos1.Count;
            Decimal totalMax = 0;
            Decimal totalMin = 0;
            Decimal subtotalMax = 0;
            Decimal subtotalMin = 0;
            for (int r = 0; r < tam; r++)
            {
                if (datos1[r].Estado == "head")
                {
                    if (tipo.Equals("prederminadoRE"))
                        ExcelWorkSheet.Cells[row + r, "A"] = datos1[r].P07ClaseDesc.Substring(1, datos1[r].P07ClaseDesc.Length - 1);
                    else
                        ExcelWorkSheet.Cells[row + r, "A"] = datos1[r].P07ClaseDesc;
                    Range head = ExcelWorkSheet.Range[ExcelWorkSheet.Cells[row + r, "A"], ExcelWorkSheet.Cells[row + r, "I"]];
                    head.Merge();
                    head.Font.Color = (Color)ColorTranslator.FromHtml("#2e2e2e");
                    head.Interior.Color = (Color)ColorTranslator.FromHtml("#c5c5c5");
                    head.Font.Bold = true;
                }
                else
                {
                    if (datos1[r].Estado == "subtotal")
                    {
                        ExcelWorkSheet.Cells[row + r, "F"] = "Sub Total";
                        ExcelWorkSheet.Cells[row + r, "h"] = subtotalMin;
                        ExcelWorkSheet.Cells[row + r, "I"] = subtotalMax;
                        subtotalMax = 0;
                        subtotalMin = 0;
                    }
                    else
                    {
                        ExcelWorkSheet.Cells[row + r, "A"] = r;
                        ExcelWorkSheet.Cells[row + r, "B"] = datos1[r].Descripcion;
                        //ExcelWorkSheet.Cells[row + r, "C"] = datos1[r].P01UnidadDesc;
                        ExcelWorkSheet.Cells[row + r, "D"] = datos1[r].Cantidad;
                        ExcelWorkSheet.Cells[row + r, "E"] = datos1[r].PrecioUnitarioMaximo;
                        ExcelWorkSheet.Cells[row + r, "F"] = datos1[r].PrecioTotalMaximo;
                        // ExcelWorkSheet.Cells[row + r, "G"] = "";
                        ExcelWorkSheet.Cells[row + r, "h"] = datos1[r].PrecioTotalMinimo;
                        ExcelWorkSheet.Cells[row + r, "I"] = datos1[r].PrecioTotalMaximo;
                        subtotalMax = subtotalMax + datos1[r].PrecioTotalMaximo;
                        subtotalMin = subtotalMin + datos1[r].PrecioTotalMinimo;
                        totalMax = totalMax + datos1[r].PrecioTotalMaximo;
                        totalMin = totalMin + datos1[r].PrecioTotalMinimo;
                    }
                }
            }
            ExcelWorkSheet.Cells[tam + row, "F"] = "TOTAL";
            ExcelWorkSheet.Cells[tam + row, "h"] = totalMin;
            ExcelWorkSheet.Cells[tam + row, "I"] = totalMax;
            //Range precio = ExcelWorkSheet.Range[ExcelWorkSheet.Cells[16, "F"], ExcelWorkSheet.Cells[16, "F"]];
            //precio.Formula = "=SUMA(F" + _inicioFila + ":F" + (tam+row) + ")";
            //ExcelWorkSheet.Range["A1"].AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic1);

            //string fileName = string.Format(@"{0}\ExcelData.xlsx", url);
            string dis = GetTimestamp(DateTime.Now);
            nombre = string.Format(@"ExcelData{0}.xlsx", dis);
            string fileName = string.Format(@"{0}\ExcelData{1}.xlsx", HttpContext.Current.Server.MapPath(url), dis);
            ExcelWorkSheet.SaveAs(fileName);

            ////for (int r = 1; r < 101; r++) //r stands for ExcelRow and c for ExcelColumn
            ////{
            ////    // Excel row and column start positions for writing Row=1 and Col=1
            ////    for (int c = 1; c < 11; c++)
            ////        ExcelWorkSheet.Cells[r, c] = "R" + r + "C" + c;
            ////}
            ////ExcelWorkBook.Worksheets[1].Name = "MySheet";//Renaming the Sheet1 to MySheet
            ////ExcelWorkBook.SaveAs("d:\\Testing.xlsx");
            ////ExcelWorkBook.Close();
            ////ExcelApp.Quit();
            ////Marshal.ReleaseComObject(ExcelWorkSheet);
            ////Marshal.ReleaseComObject(ExcelWorkBook);
            ////Marshal.ReleaseComObject(ExcelApp);
            return nombre;
        }
        catch (Exception exHandle)
        {
            Console.WriteLine("Exception: " + exHandle.Message);
            Console.ReadLine();
            return nombre;
        }
        finally
        {

            ExcelApp.Quit();

            // Release COM objects (very important!)
            if (ExcelApp != null)
                System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelApp);

            if (ExcelWorkSheet != null)
                System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelWorkSheet);

            // Empty variables
            ExcelApp = null;
            ExcelWorkSheet = null;

            // Force garbage collector cleaning
            GC.Collect();

        }

    }

    public static String GetTimestamp(DateTime value)
    {
        return value.ToString("yyyyMMddHHmmssffff");
    }

    public String GenerarExcel(List<GrupoBE> datos)
    {
        Application ExcelApp = new Application();
        Workbook ExcelWorkBook = null;
        Worksheet ExcelWorkSheet = null;
        var url = ConfigurationManager.AppSettings["urlExcel"].ToString();
        ExcelApp.Workbooks.Add();
        ExcelWorkSheet = (Worksheet)ExcelApp.ActiveSheet;
        string nombre = "";

        GrupoDetalleBL objGrupoDetalle = new GrupoDetalleBL();
        List<GrupoDetalleBE> grupoDetalle = new List<GrupoDetalleBE>();
        int indiceAnterior = 0;
        for (int i = 0; i < datos.Count; i++)
        {
            GrupoDetalleBE headTipo = new GrupoDetalleBE();
            if (i == 0)
            {
                headTipo.Descripcion = datos[i].P05TipoDesc;
                headTipo.Cantidad = i + 1;
                headTipo.Estado = "tipo";
                grupoDetalle.Add(headTipo);
            }
            if (datos[indiceAnterior].P05Tipo != datos[i].P05Tipo)
            {
                headTipo.Descripcion = datos[i].P05TipoDesc;
                headTipo.Cantidad = i + 1;
                headTipo.Estado = "tipo";
                grupoDetalle.Add(headTipo);
            }
            GrupoDetalleBE head = new GrupoDetalleBE();
            head.Descripcion = datos[i].Nombre;
            head.Estado = "head";
            GrupoDetalleBE subtotal = new GrupoDetalleBE();
            subtotal.Estado = "subtotal";
            grupoDetalle.Add(head);
            grupoDetalle.AddRange(objGrupoDetalle.ObtenerGruposDetByGrupo(datos[i].IdGrupo));
            grupoDetalle.Add(subtotal);
            indiceAnterior = i;
        }

        try
        {
            //ExcelWorkSheet.Cells[_inicioCelda, "A"] = "ID";
            //ExcelApp.Range["A" + _inicioCelda].EntireColumn.ColumnWidth = 3;
            //ExcelWorkSheet.Cells[_inicioCelda, "B"] = "Descripción";
            //ExcelApp.Range["B" + _inicioCelda].EntireColumn.ColumnWidth = 40;
            //ExcelApp.Range["B" + _inicioCelda].EntireColumn.WrapText = true;
            //ExcelWorkSheet.Cells[_inicioCelda, "C"] = "Unidad";
            //ExcelApp.Range["C" + _inicioCelda].EntireColumn.ColumnWidth = 0;
            //ExcelWorkSheet.Cells[_inicioCelda, "D"] = "Cantidad";
            //ExcelApp.Range["D" + _inicioCelda].EntireColumn.ColumnWidth = 10;
            //ExcelWorkSheet.Cells[_inicioCelda, "E"] = "Precio Unitario Mínimo";
            //ExcelApp.Range["E" + _inicioCelda].EntireColumn.ColumnWidth = 15;
            //ExcelWorkSheet.Cells[_inicioCelda, "F"] = "Precio Unitario Máximo";
            //ExcelApp.Range["F" + _inicioCelda].EntireColumn.ColumnWidth = 15;
            //ExcelWorkSheet.Cells[_inicioCelda, "G"] = "Precio Total";
            //ExcelApp.Range["G" + _inicioCelda].EntireColumn.ColumnWidth = 12;
            //ExcelWorkSheet.Cells[_inicioCelda, "h"] = "Precio Total Mínimo";
            //ExcelApp.Range["H" + _inicioCelda].EntireColumn.ColumnWidth = 20;
            //ExcelWorkSheet.Cells[_inicioCelda, "I"] = "Precio Total Máximo";
            //ExcelApp.Range["I" + _inicioCelda].EntireColumn.ColumnWidth = 20;

            int row = _inicioFila; // start row (in row 1 are header cells)

            int tam = grupoDetalle.Count;
            Decimal totalMax = 0;
            Decimal totalMin = 0;
            Decimal subtotalMax = 0;
            Decimal subtotalMin = 0;
            int indiceTipo = 1;
            for (int r = 0; r < tam; r++)
            {
                switch (grupoDetalle[r].Estado)
                {
                    case "tipo":
                        ExcelWorkSheet.Cells[row + r, "A"] = indiceTipo;
                        ExcelApp.Range["A" + row + r].EntireColumn.ColumnWidth = 3;
                        ExcelWorkSheet.Cells[row + r, "B"] = grupoDetalle[r].Descripcion.ToUpper();
                        ExcelApp.Range["B" + row + r].EntireColumn.ColumnWidth = 40;
                        ExcelApp.Range["B" + row + r].EntireColumn.WrapText = true;
                        ExcelWorkSheet.Cells[row + r, "C"] = "UNIDAD";
                        ExcelApp.Range["C" + row + r].EntireColumn.ColumnWidth = 0;
                        ExcelWorkSheet.Cells[row + r, "D"] = "CANTIDAD";
                        ExcelApp.Range["D" + row + r].EntireColumn.ColumnWidth = 10;
                        ExcelWorkSheet.Cells[row + r, "E"] = "PRECIO UNITARIO MÍNIMO";
                        ExcelApp.Range["E" + row + r].EntireColumn.ColumnWidth = 20;
                        ExcelWorkSheet.Cells[row + r, "F"] = "PRECIO UNITARIO MÁXIMO";
                        ExcelApp.Range["F" + row + r].EntireColumn.ColumnWidth = 20;
                        ExcelWorkSheet.Cells[row + r, "G"] = "PRECIO TOTAL";
                        ExcelApp.Range["G" + row + r].EntireColumn.ColumnWidth = 12;
                        ExcelWorkSheet.Cells[row + r, "h"] = "PRECIO TOTAL MÍNIMO";
                        ExcelApp.Range["H" + row + r].EntireColumn.ColumnWidth = 20;
                        ExcelWorkSheet.Cells[row + r, "I"] = "PRECIO TOTAL MÁXIMO";
                        ExcelApp.Range["I" + row + r].EntireColumn.ColumnWidth = 20;

                        //ExcelWorkSheet.Cells[row + r, "A"] = grupoDetalle[r].Descripcion;
                        Range headTipo = ExcelWorkSheet.Range[ExcelWorkSheet.Cells[row + r, "A"], ExcelWorkSheet.Cells[row + r, "I"]];
                        //headTipo.Merge();
                        //headTipo.Font.Color = (Color)ColorTranslator.FromHtml("#00B050");
                        headTipo.Interior.Color = (Color)ColorTranslator.FromHtml("#00B050");
                        headTipo.Font.Bold = true;
                        headTipo.Font.Size = 9;
                        headTipo.RowHeight = 30;
                        headTipo.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                        headTipo.VerticalAlignment = XlHAlign.xlHAlignCenter;
                        indiceTipo++;
                        break;
                    case "head":
                        ExcelWorkSheet.Cells[row + r, "A"] = grupoDetalle[r].Descripcion;
                        Range head = ExcelWorkSheet.Range[ExcelWorkSheet.Cells[row + r, "A"], ExcelWorkSheet.Cells[row + r, "I"]];
                        head.Merge();
                        //head.Font.Color = (Color)ColorTranslator.FromHtml("#2e2e2e");
                        head.Interior.Color = (Color)ColorTranslator.FromHtml("#92D050");
                        head.Font.Bold = true;
                        head.Font.Size = 9;
                        head.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                        break;
                    case "subtotal":
                        ExcelWorkSheet.Cells[row + r, "g"] = "Sub Total";
                        ExcelWorkSheet.Cells[row + r, "h"] = subtotalMin;
                        ExcelWorkSheet.Cells[row + r, "I"] = subtotalMax;
                        subtotalMax = 0;
                        subtotalMin = 0;
                        break;
                    default:
                        ExcelWorkSheet.Cells[row + r, "A"] = r;
                        ExcelWorkSheet.Cells[row + r, "B"] = grupoDetalle[r].IdComponenteDesc;
                        ExcelWorkSheet.Cells[row + r, "C"] = grupoDetalle[r].P01UnidadDesc; ;
                        ExcelWorkSheet.Cells[row + r, "D"] = grupoDetalle[r].Cantidad;
                        ExcelWorkSheet.Cells[row + r, "E"] = grupoDetalle[r].PrecioMinimo;
                        ExcelWorkSheet.Cells[row + r, "F"] = grupoDetalle[r].PrecioMaximo;
                        ExcelWorkSheet.Cells[row + r, "G"] = grupoDetalle[r].PrecioTotalMaximo;
                        ExcelWorkSheet.Cells[row + r, "h"] = grupoDetalle[r].PrecioTotalMinimo;
                        ExcelWorkSheet.Cells[row + r, "I"] = grupoDetalle[r].PrecioTotalMaximo;
                        subtotalMax = subtotalMax + grupoDetalle[r].PrecioTotalMaximo;
                        subtotalMin = subtotalMin + grupoDetalle[r].PrecioTotalMinimo;
                        totalMax = totalMax + grupoDetalle[r].PrecioTotalMaximo;
                        totalMin = totalMin + grupoDetalle[r].PrecioTotalMinimo;
                        break;
                }
            }
            ExcelWorkSheet.Cells[tam + row + 1, "g"] = "TOTAL";
            ExcelWorkSheet.Cells[tam + row + 1, "h"] = totalMin;
            ExcelWorkSheet.Cells[tam + row + 1, "I"] = totalMax;
            ////Range precio = ExcelWorkSheet.Range[ExcelWorkSheet.Cells[16, "F"], ExcelWorkSheet.Cells[16, "F"]];
            ////precio.Formula = "=SUMA(F" + _inicioFila + ":F" + (tam+row) + ")";
            ////ExcelWorkSheet.Range["A1"].AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic1);

            ////string fileName = string.Format(@"{0}\ExcelData.xlsx", url);
            string dis = GetTimestamp(DateTime.Now);
            nombre = string.Format(@"ExcelData{0}.xlsx", dis);
            string fileName = string.Format(@"{0}\ExcelData{1}.xlsx", HttpContext.Current.Server.MapPath(url), dis);
            ExcelWorkSheet.SaveAs(fileName);
            return nombre;
        }
        catch (Exception exHandle)
        {
            Console.WriteLine("Exception: " + exHandle.Message);
            Console.ReadLine();
            return nombre;
        }
        finally
        {

            ExcelApp.Quit();
            if (ExcelApp != null)
                System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelApp);

            if (ExcelWorkSheet != null)
                System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelWorkSheet);

            ExcelApp = null;
            ExcelWorkSheet = null;
            GC.Collect();
        }
    }

    public String GenerarExcelComponentes(List<ComponenteBE> datos)
    {
        Application ExcelApp = new Application();
        Worksheet ExcelWorkSheet = null;
        var url = ConfigurationManager.AppSettings["urlExcel"].ToString();
        ExcelApp.Workbooks.Add();
        ExcelWorkSheet = (Worksheet)ExcelApp.ActiveSheet;
        string nombre = "";

        try
        {
            ExcelWorkSheet.Cells[_inicioCelda, "A"] = "";
            ExcelApp.Range["A" + _inicioCelda].EntireColumn.ColumnWidth = 3;
            ExcelWorkSheet.Cells[_inicioCelda, "B"] = "Codigo";
            ExcelApp.Range["B" + _inicioCelda].EntireColumn.ColumnWidth = 10;
            ExcelWorkSheet.Cells[_inicioCelda, "C"] = "Descripcion";
            ExcelApp.Range["C" + _inicioCelda].EntireColumn.ColumnWidth = 70;
            ExcelApp.Range["C" + _inicioCelda].EntireColumn.WrapText = true;
            ExcelWorkSheet.Cells[_inicioCelda, "D"] = "Unidad";
            ExcelApp.Range["D" + _inicioCelda].EntireColumn.ColumnWidth = 15;
            ExcelWorkSheet.Cells[_inicioCelda, "E"] = "Clase";
            ExcelApp.Range["E" + _inicioCelda].EntireColumn.ColumnWidth = 20;
            ExcelWorkSheet.Cells[_inicioCelda, "F"] = "Precio Unitario Minimo";
            ExcelApp.Range["F" + _inicioCelda].EntireColumn.ColumnWidth = 20;
            ExcelWorkSheet.Cells[_inicioCelda, "G"] = "Precio Unitario Maximo";
            ExcelApp.Range["G" + _inicioCelda].EntireColumn.ColumnWidth = 10;
            ExcelWorkSheet.Cells[_inicioCelda, "H"] = "Tiempo Vida Anual";
            ExcelApp.Range["H" + _inicioCelda].EntireColumn.ColumnWidth = 10;
            ExcelWorkSheet.Cells[_inicioCelda, "I"] = "Fecha Creacion";
            ExcelApp.Range["I" + _inicioCelda].EntireColumn.ColumnWidth = 20;
            ExcelWorkSheet.Cells[_inicioCelda, "J"] = "Informacion Publica";
            ExcelApp.Range["J" + _inicioCelda].EntireColumn.ColumnWidth = 10;
            ExcelWorkSheet.Cells[_inicioCelda, "K"] = "Capacidad";
            ExcelApp.Range["K" + _inicioCelda].EntireColumn.ColumnWidth = 50;



            int row = _inicioFila; // start row (in row 1 are header cells)
            int tam = datos.Count;
            for (int r = 0; r < tam; r++)
            {
                //ExcelWorkSheet.Cells[row + r, "A"] = r;
                ExcelWorkSheet.Cells[row + r, "B"] = datos[r].IdComponente;
                ExcelWorkSheet.Cells[row + r, "C"] = datos[r].Descripcion;
                ExcelWorkSheet.Cells[row + r, "D"] = datos[r].P01UnidadDesc;
                ExcelWorkSheet.Cells[row + r, "E"] = datos[r].P07ClaseDesc;
                ExcelWorkSheet.Cells[row + r, "F"] = datos[r].PrecioUnitarioMinimo;
                ExcelWorkSheet.Cells[row + r, "G"] = datos[r].PrecioUnitarioMaximo;
                ExcelWorkSheet.Cells[row + r, "h"] = datos[r].TiempoVidaAnio;
                ExcelWorkSheet.Cells[row + r, "I"] = datos[r].FecCre;
                if (datos[r].InfoPublica == 1)
                {
                    ExcelWorkSheet.Cells[row + r, "J"] = "SI";
                }
                else
                {
                    ExcelWorkSheet.Cells[row + r, "J"] = "NO";
                }
                ExcelWorkSheet.Cells[row + r, "K"] = datos[r].Capacidad;
            }
            string dis = GetTimestamp(DateTime.Now);
            nombre = string.Format(@"ExcelData{0}.xlsx", dis);
            string fileName = string.Format(@"{0}\ExcelData{1}.xlsx", HttpContext.Current.Server.MapPath(url), dis);
            ExcelWorkSheet.SaveAs(fileName);
            return nombre;
        }
        catch (Exception exHandle)
        {
            Console.WriteLine("Exception: " + exHandle.Message);
            Console.ReadLine();
            return nombre;
        }
        finally
        {

            ExcelApp.Quit();
            if (ExcelApp != null)
                System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelApp);

            if (ExcelWorkSheet != null)
                System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelWorkSheet);

            ExcelApp = null;
            ExcelWorkSheet = null;
            GC.Collect();
        }
    }


    public String GenerarExcelComponentesPublicos(List<ComponenteBE> datosPublicos)
    {
        List<ComponenteBE> datos = new List<ComponenteBE>();
        for (int i = 0; i < datosPublicos.Count; i++)
        {
            if (datosPublicos[i].InfoPublica == 1)
            {
                datos.Add(datosPublicos[i]);
            }
        }
        Application ExcelApp = new Application();
        Worksheet ExcelWorkSheet = null;
        var url = ConfigurationManager.AppSettings["urlExcel"].ToString();
        ExcelApp.Workbooks.Add();
        ExcelWorkSheet = (Worksheet)ExcelApp.ActiveSheet;
        string nombre = "";

        try
        {
            ExcelWorkSheet.Cells[_inicioCelda, "A"] = "";
            ExcelApp.Range["A" + _inicioCelda].EntireColumn.ColumnWidth = 3;
            ExcelWorkSheet.Cells[_inicioCelda, "B"] = "Codigo";
            ExcelApp.Range["B" + _inicioCelda].EntireColumn.ColumnWidth = 10;
            ExcelWorkSheet.Cells[_inicioCelda, "C"] = "Descripcion";
            ExcelApp.Range["C" + _inicioCelda].EntireColumn.ColumnWidth = 70;
            ExcelApp.Range["C" + _inicioCelda].EntireColumn.WrapText = true;
            ExcelWorkSheet.Cells[_inicioCelda, "D"] = "Unidad";
            ExcelApp.Range["D" + _inicioCelda].EntireColumn.ColumnWidth = 15;
            ExcelWorkSheet.Cells[_inicioCelda, "E"] = "Clase";
            ExcelApp.Range["E" + _inicioCelda].EntireColumn.ColumnWidth = 20;
            ExcelWorkSheet.Cells[_inicioCelda, "F"] = "Precio Unitario Minimo";
            ExcelApp.Range["F" + _inicioCelda].EntireColumn.ColumnWidth = 20;
            ExcelWorkSheet.Cells[_inicioCelda, "G"] = "Precio Unitario Maximo";
            ExcelApp.Range["G" + _inicioCelda].EntireColumn.ColumnWidth = 10;
            ExcelWorkSheet.Cells[_inicioCelda, "H"] = "Tiempo Vida Anual";
            ExcelApp.Range["H" + _inicioCelda].EntireColumn.ColumnWidth = 10;
            ExcelWorkSheet.Cells[_inicioCelda, "I"] = "Fecha Creacion";
            ExcelApp.Range["I" + _inicioCelda].EntireColumn.ColumnWidth = 20;
            ExcelWorkSheet.Cells[_inicioCelda, "J"] = "Capacidad";
            ExcelApp.Range["J" + _inicioCelda].EntireColumn.ColumnWidth = 50;



            int row = _inicioFila; // start row (in row 1 are header cells)
            int tam = datos.Count;
            for (int r = 0; r < tam; r++)
            {
                //ExcelWorkSheet.Cells[row + r, "A"] = r;
                ExcelWorkSheet.Cells[row + r, "B"] = datos[r].IdComponente;
                ExcelWorkSheet.Cells[row + r, "C"] = datos[r].Descripcion;
                ExcelWorkSheet.Cells[row + r, "D"] = datos[r].P01UnidadDesc;
                ExcelWorkSheet.Cells[row + r, "E"] = datos[r].P07ClaseDesc;
                ExcelWorkSheet.Cells[row + r, "F"] = datos[r].PrecioUnitarioMinimo;
                ExcelWorkSheet.Cells[row + r, "G"] = datos[r].PrecioUnitarioMaximo;
                ExcelWorkSheet.Cells[row + r, "h"] = datos[r].TiempoVidaAnio;
                ExcelWorkSheet.Cells[row + r, "I"] = datos[r].FecCre;
                ExcelWorkSheet.Cells[row + r, "J"] = datos[r].Capacidad;
            }
            string dis = GetTimestamp(DateTime.Now);
            nombre = string.Format(@"ExcelData{0}.xlsx", dis);
            string fileName = string.Format(@"{0}\ExcelData{1}.xlsx", HttpContext.Current.Server.MapPath(url), dis);
            ExcelWorkSheet.SaveAs(fileName);
            return nombre;
        }
        catch (Exception exHandle)
        {
            Console.WriteLine("Exception: " + exHandle.Message);
            Console.ReadLine();
            return nombre;
        }
        finally
        {

            ExcelApp.Quit();
            if (ExcelApp != null)
                System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelApp);

            if (ExcelWorkSheet != null)
                System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelWorkSheet);

            ExcelApp = null;
            ExcelWorkSheet = null;
            GC.Collect();
        }
    }
}